﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class ServiceCheckNotLatestServicePack :IssueTypeCheckBase
    {

        public ServiceCheckNotLatestServicePack()
        {
            IssueTypeId = 2001;
        }

        public override void Check()
        {
            HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
            Issue issue;
            //Query database (SERVICE TABLE)for service packs installed on each db 


            String maxSP2005 = dc.SQLServerVersions.Where(s => s.Version.StartsWith("9")).Max(s => s.Version);
            String maxSP2008 = dc.SQLServerVersions.Where(s => s.Version.StartsWith("10")).Max(s => s.Version);
            String maxSP2008R2 = dc.SQLServerVersions.Where(s => s.Version.StartsWith("10.5")).Max(s => s.Version);



            var ServiceQry = from s in dc.Services

                             where(s.ProductVersion.StartsWith("9") && (s.ProductVersion.CompareTo(maxSP2005)<0)
                             ||

                              (s.ProductVersion.StartsWith("10")) && (s.ProductVersion.CompareTo(maxSP2008)< 0)

                               ||

                               (s.ProductVersion.StartsWith("10.5")) && (s.ProductVersion.CompareTo(maxSP2008R2)<0))
                               
                             
                       select s;

            
            foreach (var item in ServiceQry)
            {
                string latestSP = "";
                if(item.ProductVersion.StartsWith("9"))
                {
                    latestSP = maxSP2005;

                }
                else if (item.ProductVersion.StartsWith("10"))
                {

                    latestSP = maxSP2008;
                }
                else if (item.ProductVersion.StartsWith("10.5"))
                {

                    latestSP = maxSP2008R2;
                }
                
               issue = new Issue();

                
                //Get Issuetype object

                //Get Issuetype object  And Create a new  Global issue object 

                IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);

                

                issue.IssueType = issueType;
                issue.TargetId = item.ServerID;
                issue.Description = issueType.Description.Replace("{ServerName}", item.Server.Name).Replace("{oldSP}", item.ProductVersion).Replace("{newSP}",latestSP);

                dc.Issues.InsertOnSubmit(issue);
            }

            dc.SubmitChanges();
        }


    }
       
}
